﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using XQ.RequestOfficeSupplies.DAL.Model;
using XQ.RequestOfficeSupplies.DAL.Model.Sys;
using XQ.RequestOfficeSupplies.DAL.Models;
using XQ.RequestOfficeSupplies.DAL;

namespace XQ.RequestOfficeSupplies.DAL.DAL
{
    /// <summary>
    /// 街道部门数据操作类
    /// </summary>
    public class DepartmentDal:BaseDal<Sys_Departments>
    {
        private RequestOfficeSuppliesEntities _ce;
        RoleDal roleDal = new RoleDal();

        /// <summary>
        /// 根据ID获取部门详情
        /// </summary>
        /// <param name="depId"></param>
        /// <returns></returns>
        public Sys_Departments GetDepById(int depId)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.FirstOrDefault(n => n.Id == depId);
            }
        }

        /// <summary>
        /// 获取部门列表
        /// </summary>
        /// <returns></returns>
        public List<DepartmentModel> GetDepartmentList(string deptName="", TokenModel token = null)
        {
            try
            {
                bool isDepartmentRole = roleDal.IsDepartmentRole(token.RoleId);
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    string sql = "";
                    if (isDepartmentRole)
                    {
                        sql = @"select dep.*,(select DepartmentName from Sys_Departments where Id=ParentId ) DepartmentPName, (select MAX( Sortnum) from Sys_Departments where ParentId=dep.id) MaxSortNum from Sys_Departments  dep where dep.id=" + token.DepartmentID;
                    }
                    else
                    {
                        sql = @"select dep.*,(select DepartmentName from Sys_Departments where Id=ParentId ) DepartmentPName, (select MAX( Sortnum) from Sys_Departments where ParentId=dep.id) MaxSortNum from Sys_Departments  dep where dep.ParentId=0 order by dep.Sortnum";
                    }
                    return _ce.Database.SqlQuery<DepartmentModel>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取部门列表(手机端)
        /// </summary>
        /// <returns></returns>
        public List<DepartmentModel> GetDeptList()
        {
            try
            {
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    string sql = @"select dep.*,(select DepartmentName from Sys_Departments where Id=ParentId ) DepartmentPName, (select MAX( Sortnum) from Sys_Departments where ParentId=dep.id) MaxSortNum from Sys_Departments  dep where  dep.ParentId=0 and dep.Code<>'' and dep.Code is not null order by dep.Sortnum";
                    return _ce.Database.SqlQuery<DepartmentModel>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
        }



        /// <summary>
        /// 获取子部门列表
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<DepartmentModel> GetDepartmentSubList(int pid)
        {
            try
            {
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    string sql = @"select dep.*,(select DepartmentName from Sys_Departments where Id=@pid ) DepartmentPName,
                                    (select MAX( Sortnum) from Sys_Departments where ParentId=dep.id) MaxSortNum   
                                    from Sys_Departments dep where dep.ParentId=@pid order by dep.Sortnum";
                    object[] par = { new SqlParameter("@pid", pid) };
                    return _ce.Database.SqlQuery<DepartmentModel>(sql, par).ToList();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 获取子部门列表（不包括住建局下的科室）
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<DepartmentModel> GetDepartmentSubListNoZZJ(int pid)
        {
            try
            {
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    string sql = @"select dep.*,(select DepartmentName from Sys_Departments where Id=@pid ) DepartmentPName,
                                    (select MAX( Sortnum) from Sys_Departments where ParentId=dep.id) MaxSortNum   
                                    from Sys_Departments dep where dep.ParentId=@pid and left(code,3)<>'ZJJ' order by dep.Sortnum";
                    object[] par = { new SqlParameter("@pid", pid) };
                    return _ce.Database.SqlQuery<DepartmentModel>(sql, par).ToList();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 获取子部门列表(手机端)
        /// </summary>
        /// <param name="pid"></param>
        /// <returns></returns>
        public List<DepartmentModel> GetDeptSubList(int pid)
        {
            try
            {
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    string sql = @"select dep.*,(select DepartmentName from Sys_Departments where Id=@pid ) DepartmentPName,
                                    (select MAX( Sortnum) from Sys_Departments where ParentId=dep.id) MaxSortNum   
                                    from Sys_Departments dep where dep.ParentId=@pid and dep.Code<>'' and dep.Code is not null order by dep.Sortnum";
                    object[] par = { new SqlParameter("@pid", pid) };
                    return _ce.Database.SqlQuery<DepartmentModel>(sql, par).ToList();
                }
            }
            catch (Exception)
            {

                throw;
            }
        }


        /// <summary>
        /// 保存部门
        /// </summary>
        /// <param name="dep">部门实体</param>
        /// <returns></returns>
        public string SaveDepartment(Sys_Departments dep)
        {
            try
            {
                int reslut = 0;

                if (CheckSameName(dep.DepartmentName, dep.Id, dep.ParentId ?? 0))
                {
                    return "same";
                }
                else
                {
                    if (dep.Id == 0)
                    {
                        using (_ce = new RequestOfficeSuppliesEntities())
                        {
                            _ce.Sys_Departments.Add(dep);
                            reslut = _ce.SaveChanges();
                        }
                    }
                    else
                    {
                        using (_ce = new RequestOfficeSuppliesEntities())
                        {
                            var department = _ce.Sys_Departments.First(n => n.Id == dep.Id);
                            department.Id = dep.Id;
                            department.ParentId = dep.ParentId;
                            department.Remark = dep.Remark;
                            department.DepartmentName = dep.DepartmentName;
                            department.Sortnum = dep.Sortnum;
                            department.Code = dep.Code;
                            reslut = _ce.SaveChanges();
                        }

                    }
                    if (reslut > 0)
                    {
                        return "success";
                    }
                    else
                    {
                        return "fail";
                    }

                }
            }
            catch (Exception)
            {

                throw;
            }

        }

        /// <summary>
        /// 检查部门重名
        /// </summary>
        /// <param name="name">名字</param>
        /// <param name="id">ID</param>
        /// <param name="parentId">父节点ID</param>
        /// <returns></returns>
        private bool CheckSameName(string name, int id, int parentId)
        {

            try
            {
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    int result = _ce.Sys_Departments.Where(n => n.DepartmentName == name && n.ParentId == parentId && n.Id != id).ToList().Count;
                    if (result > 0)
                        return true;
                    else
                        return false;
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 删除部门
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string DeleteDepartment(int id)
        {
            try
            {
                using (_ce = new RequestOfficeSuppliesEntities())
                {
                    const string sql = @"if  exists (select * from Sys_Users where DepartmentID=@pid)
                            begin
                            select 0
                            end
                            else
                            begin
                            delete from Sys_Departments where id=@pid
                            select 1
                            end";
                    object[] par = { new SqlParameter("@pid", id) };

                    int result = _ce.Database.SqlQuery<int>(sql, par).ToList()[0];
                    if (result > 0)
                    {
                        return "success";
                    }
                    else
                    {
                        return "have";
                    }

                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 删除部门(多删)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool DeleteDepartmentByIds(List<int> ids)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                if (ids != null && ids.Count > 0)
                {
                    foreach (int id in ids)
                    {
                        Sys_Departments department = _ce.Sys_Departments.FirstOrDefault(d => d.Id == id);
                        if (department != null)
                        {
                            _ce.Sys_Departments.Remove(department);
                        }
                    }
                    return _ce.SaveChanges() > 0;
                }
                return false;
            }
        }

        /// <summary>
        /// 根据ID返回部门名称
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetDepartmentName(int id)
        {
            if (id == 0)
                return "";
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.First(n => n.Id == id).DepartmentName;
            }
        }

        /// <summary>
        /// 根据ID获取部门CODE
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetDepartmentCode(int id)
        {
            if (id == 0)
                return "";
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.First(n => n.Id == id).Code;
            }
        }

        /// <summary>
        /// 获取部门ID
        /// </summary>
        /// <param name="depID">部门ID</param>
        /// <param name="dp"></param>
        /// <returns></returns>
        public int GetDepIDbyDepth(int depID, int dp)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                int depth =
                    _ce.Database.SqlQuery<int>("select depth from Sys_Departments where id=@depID",
                        new object[] { new SqlParameter("@depID", depID) }).FirstOrDefault();
                if (dp <= depth)
                {
                    return depID;
                }
                else
                {
                    int newID = _ce.Database.SqlQuery<int>("select ParentId from Sys_Departments id=@depID",
                          new object[] { new SqlParameter("@depID", depID) }).FirstOrDefault();
                    return GetDepIDbyDepth(newID, dp);
                }

            }
        }

        /// <summary>
        ///返回所有部门按指定Depth
        /// </summary>
        /// <param name="depth"></param>
        /// <returns></returns>
        public List<Sys_Departments> GetDepartmentsByDepth(int depth)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Database.SqlQuery<Sys_Departments>("select * from Sys_Departments where depth=@depth", new object[] { new SqlParameter("@depth", depth) }).ToList();
            }
        }

        /// <summary>
        /// 获取有部门编号（有边界）的二级部门
        /// </summary>
        /// <returns></returns>
        public List<Sys_Boundary> GetHasCodeDept()
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                List<Sys_Departments> depts = _ce.Sys_Departments.Where(d => d.Code != "" && d.depth == 2).ToList();
                List<int> ids = new List<int>();
                foreach (Sys_Departments d in depts)
                {
                    ids.Add(d.Id);
                }

                return _ce.Sys_Boundary.Where(b => ids.Contains((int)b.deptpartmentID)).ToList();
            }
        }

        /// <summary>
        /// 根据父部门获取子部门的边界
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public List<Sys_Boundary> GetDeptByParentId(int parentId)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                List<Sys_Departments> depts = _ce.Sys_Departments.Where(d => d.Code != "" && d.ParentId == parentId).ToList();
                List<int> ids = new List<int>();
                foreach (Sys_Departments d in depts)
                {
                    ids.Add(d.Id);
                }

                return _ce.Sys_Boundary.Where(b => ids.Contains((int)b.deptpartmentID)).ToList();
            }
        }

        /// <summary>
        /// 根据区域代码返回部门路径
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public string GetAllDepartmentName(string code)
        {
            if (code == "")
                return "";
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                StringBuilder sql = new StringBuilder("with newDep as(select * from Sys_Departments where Sys_Departments.Code = @code ");
                sql.Append(" union all select Sys_Departments.* from newDep  inner join Sys_Departments on newDep.ParentId = Sys_Departments.id ");
                sql.Append(" where Sys_Departments.ParentId<>0 ) ");
                sql.Append(" select * from newDep order by depth;");
                SqlParameter[] par = { new SqlParameter("@code", code) };
                List<Sys_Departments> res = _ce.Database.SqlQuery<Sys_Departments>(sql.ToString(), par).ToList();
                if (res.Any())
                {
                    string result = "";
                    foreach (var r in res)
                    {
                        result += (result == "" ? "" : "\\") + r.DepartmentName;
                    }
                    return result;
                }
                else
                {
                    return "";
                }
            }
        }
        /// <summary>
        /// 根据部门ID返回部门路径
        /// </summary>
        /// <param name="depID"></param>
        /// <returns></returns>
        public string GetAllDepartmentName(int depID)
        {
            if (depID == 0)
                return "";
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                StringBuilder sql = new StringBuilder("with newDep as(select * from Sys_Departments where Sys_Departments.Id = @depId ");
                sql.Append(" union all select Sys_Departments.* from newDep  inner join Sys_Departments on newDep.ParentId = Sys_Departments.id ");
                sql.Append(" where Sys_Departments.ParentId<>0 ) ");
                sql.Append(" select * from newDep order by depth;");
                SqlParameter[] par = { new SqlParameter("@depId", depID) };
                List<Sys_Departments> res = _ce.Database.SqlQuery<Sys_Departments>(sql.ToString(), par).ToList();
                if (res.Any())
                {
                    string result = "";
                    foreach (var r in res)
                    {
                        result += (result == "" ? "" : "\\") + r.DepartmentName;
                    }
                    return result;
                }
                else
                {
                    return "";
                }
            }
        }

        /// <summary>
        /// 根据部门ID查找中心点
        /// </summary>
        /// <param name="deptId"></param>
        /// <returns></returns>
        public string GetCenterByDeptID(int deptId)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                Sys_Departments dept = _ce.Sys_Departments.First(d => d.Id == deptId);
                if (dept != null)
                {
                    return dept.lat + "," + dept.lng;
                }
                return "";
            }
        }

        /// <summary>
        /// 获取部门名称
        /// </summary>
        /// <param name="code">部门代码</param>
        /// <returns></returns>
        public string GetDepartmentNameByCode(string code)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                var sysDepartments = _ce.Sys_Departments.FirstOrDefault(d => d.Code == code);
                return sysDepartments == null ? "" : sysDepartments.DepartmentName;
            }
        }
        /// <summary>
        /// 获取部门ID
        /// </summary>
        /// <param name="code">部门代码</param>
        /// <returns></returns>
        public int GetDepIdByCode(string code)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                var sysDepartments = _ce.Sys_Departments.FirstOrDefault(d => d.Code == code);
                return sysDepartments==null?0 : sysDepartments.Id;
            }
        }

        /// <summary>
        /// 获取指定层级下的所有部门
        /// </summary>
        /// <returns></returns>
        public List<Sys_Departments> GetAllDepartmentByDepth(int depth)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.Where(d => d.depth >= depth).ToList();
            }
        }

        /// <summary>
        /// 获取所有行政村
        /// </summary>
        /// <returns></returns>
        public List<Sys_Departments> GetDepartmentsByCun()
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                List<Sys_Departments> depts = _ce.Sys_Departments.Where(d => d.Code != "" && d.depth == 2).ToList();

                return depts;
            }
        }

        /// <summary>
        /// 获取所有街道部门ID
        /// </summary>
        /// <returns></returns>
        public List<int> GetIDsByJDBM()
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.Where(d => d.Code.StartsWith("3202020062") && d.depth == 3).Select(d => d.Id).ToList();
            }
        }

        /// <summary>
        /// 获取所有社区部门ID
        /// </summary>
        /// <returns></returns>
        public List<int> GetIDsBySQBM()
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.Where(d => d.Code.StartsWith("3202020060") && d.depth == 3).Select(d => d.Id).ToList();
            }
        }

        /// <summary>
        /// 获取所有平台公司部门ID
        /// </summary>
        /// <returns></returns>
        public List<int> GetIDsByPTGSBM()
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.Where(d => d.Code.StartsWith("3202020063") && d.depth == 3).Select(d => d.Id).ToList();
            }
        }

        /// <summary>
        /// 获取所有部门
        /// </summary>
        /// <returns></returns>
        public List<TreeModel> GetTree()
        {
            List<TreeModel> list = new List<TreeModel>();
            using (RequestOfficeSuppliesEntities he = new RequestOfficeSuppliesEntities())
            {
                StringBuilder sql = new StringBuilder();
                sql.Append("select ID as id,DepartmentName as name ,ParentId as parentid from Sys_Departments");
                list = he.Database.SqlQuery<TreeModel>(sql.ToString()).ToList();
            }
            return list;
        }
   

        /// <summary>
        /// 获取单位性质
        /// </summary>
        /// <returns></returns>
        public List<Sys_Departments> GetDepartmentByType(int? Id, string deptName = "", TokenModel token=null)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                List<Sys_Departments> depts;
                bool isDepartmentRole = roleDal.IsDepartmentRole(token.RoleId);
                if (Id != 0)
                {
                    depts = _ce.Sys_Departments.Where(d => d.ParentId == Id && d.DepartmentName.Contains(deptName)).ToList();
                    return depts;
                }
                else {
                    depts = _ce.Sys_Departments.Where(d => d.ParentId != 0 && (isDepartmentRole? d.ParentId==token.DepartmentID:true) && d.DepartmentName.Contains(deptName)).ToList();
                }
                return depts;
            }
        }

        /// <summary>
        /// 获取指定部门下面的所有子部门ID
        /// </summary>
        /// <param name="deptId"></param>
        /// <returns></returns>
        public List<int> GetAllChildDeptIDs(int deptId)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                List<int> allList = new List<int>();
                List<int> curList = _ce.Sys_Departments.Where(d => d.ParentId == deptId).Select(d => d.Id).ToList();
                //添加当前层级部门ID
                allList.AddRange(curList);
                foreach (int dID in curList)
                {
                    allList.AddRange(GetAllChildDeptIDs(dID));
                }
                return allList;
            }
        }

        /// <summary>
        /// 检查当前部门是否为顶级部门
        /// </summary>
        /// <param name="departmentID"></param>
        /// <returns></returns>
        public bool IsTopDept(int departmentID)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                Sys_Departments dept = _ce.Sys_Departments.FirstOrDefault(d => d.Id == departmentID);
                if (dept != null)
                {
                    return dept.ParentId == 0;
                }
                return false;
            }
        }

        /// <summary>
        /// 获取指定部门下面的所有子部门
        /// </summary>
        /// <param name="parentID"></param>
        /// <returns></returns>
        public List<Sys_Departments> GetAllChildDeptByParentID(int parentID)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.Where(d => d.ParentId == parentID && !d.Code.StartsWith("ZJJ") && d.Remark != "StreetDepartment" ).ToList();
            }
        }

        /// <summary>
        /// 获取指定部门下面的所有非社区部门
        /// </summary>
        /// <param name="parentID"></param>
        /// <returns></returns>
        public List<Sys_Departments> GetStreetDepartmentByParentID(int parentID)
        {
            using (_ce = new RequestOfficeSuppliesEntities())
            {
                return _ce.Sys_Departments.Where(d => d.ParentId == parentID && d.Remark == "StreetDepartment").ToList();
            }
        }
    }
}
